package com.company.ems.controller;

import com.company.ems.dto.EmployeeQueryDTO;
import com.company.ems.dto.PageDTO;
import com.company.ems.model.Employee;
import com.company.ems.model.User;
import com.company.ems.util.DatabaseUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/employees")
public class EmployeeServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 设置字符编码
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");

        String action = request.getParameter("action");

        if ("view".equals(action)) {
            viewEmployee(request, response);
        } else if ("edit".equals(action)) {
            editEmployeeForm(request, response);
        } else if ("add".equals(action)) {
            request.getRequestDispatcher("/employee/add.jsp").forward(request, response);
        } else {
            listEmployees(request, response);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 设置字符编码
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");

        String action = request.getParameter("action");

        if ("add".equals(action)) {
            addEmployee(request, response);
        } else if ("update".equals(action)) {
            updateEmployee(request, response);
        } else if ("delete".equals(action)) {
            deleteEmployee(request, response);
        }
    }

    private void listEmployees(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            EmployeeQueryDTO query = new EmployeeQueryDTO();
            query.setName(request.getParameter("name"));
            query.setDepartment(request.getParameter("department"));
            query.setPosition(request.getParameter("position"));

            String pageStr = request.getParameter("page");
            if (pageStr != null && !pageStr.isEmpty()) {
                query.setPage(Integer.parseInt(pageStr));
            }

            conn = DatabaseUtil.getConnection();

            // 构建查询条件
            StringBuilder sqlBuilder = new StringBuilder(
                    "SELECT e.*, u.username as created_by_name FROM employees e " +
                            "LEFT JOIN users u ON e.created_by = u.id WHERE 1=1"
            );
            List<Object> params = new ArrayList<>();

            if (query.getName() != null && !query.getName().isEmpty()) {
                sqlBuilder.append(" AND e.name LIKE ?");
                params.add("%" + query.getName() + "%");
            }

            if (query.getDepartment() != null && !query.getDepartment().isEmpty()) {
                sqlBuilder.append(" AND e.department = ?");
                params.add(query.getDepartment());
            }

            if (query.getPosition() != null && !query.getPosition().isEmpty()) {
                sqlBuilder.append(" AND e.position = ?");
                params.add(query.getPosition());
            }

            sqlBuilder.append(" ORDER BY e.id DESC LIMIT ?, ?");
            params.add(query.getOffset());
            params.add(query.getPageSize());

            // 执行查询
            pstmt = conn.prepareStatement(sqlBuilder.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            List<Employee> employees = new ArrayList<>();

            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date").toLocalDate());
                employee.setCreatedByName(rs.getString("created_by_name"));
                employees.add(employee);
            }

            // 查询总数
            StringBuilder countSqlBuilder = new StringBuilder("SELECT COUNT(*) FROM employees e WHERE 1=1");
            List<Object> countParams = new ArrayList<>();

            if (query.getName() != null && !query.getName().isEmpty()) {
                countSqlBuilder.append(" AND e.name LIKE ?");
                countParams.add("%" + query.getName() + "%");
            }

            if (query.getDepartment() != null && !query.getDepartment().isEmpty()) {
                countSqlBuilder.append(" AND e.department = ?");
                countParams.add(query.getDepartment());
            }

            if (query.getPosition() != null && !query.getPosition().isEmpty()) {
                countSqlBuilder.append(" AND e.position = ?");
                countParams.add(query.getPosition());
            }

            pstmt = conn.prepareStatement(countSqlBuilder.toString());
            for (int i = 0; i < countParams.size(); i++) {
                pstmt.setObject(i + 1, countParams.get(i));
            }

            rs = pstmt.executeQuery();
            int total = 0;
            if (rs.next()) {
                total = rs.getInt(1);
            }

            PageDTO<Employee> page = new PageDTO<>(employees, total, query.getPage(), query.getPageSize());

            request.setAttribute("page", page);
            request.setAttribute("query", query);
            request.getRequestDispatcher("/employee/list.jsp").forward(request, response);

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "查询员工列表失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, rs);
        }
    }

    private void viewEmployee(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            int id = Integer.parseInt(request.getParameter("id"));
            conn = DatabaseUtil.getConnection();

            String sql = "SELECT e.*, u.username as created_by_name FROM employees e " +
                    "LEFT JOIN users u ON e.created_by = u.id WHERE e.id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date").toLocalDate());
                employee.setCreatedByName(rs.getString("created_by_name"));
                employee.setCreatedAt(rs.getTimestamp("created_at") != null ?
                        rs.getTimestamp("created_at").toLocalDateTime() : null);
                employee.setUpdatedAt(rs.getTimestamp("updated_at") != null ?
                        rs.getTimestamp("updated_at").toLocalDateTime() : null);

                request.setAttribute("employee", employee);
                request.getRequestDispatcher("/employee/view.jsp").forward(request, response);
            } else {
                request.setAttribute("error", "员工不存在");
                request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "查询员工详情失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, rs);
        }
    }

    private void editEmployeeForm(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            int id = Integer.parseInt(request.getParameter("id"));
            conn = DatabaseUtil.getConnection();

            String sql = "SELECT * FROM employees WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setDepartment(rs.getString("department"));
                employee.setPosition(rs.getString("position"));
                employee.setSalary(rs.getBigDecimal("salary"));
                employee.setEmail(rs.getString("email"));
                employee.setPhone(rs.getString("phone"));
                employee.setHireDate(rs.getDate("hire_date").toLocalDate());

                request.setAttribute("employee", employee);
                request.getRequestDispatcher("/employee/edit.jsp").forward(request, response);
            } else {
                request.setAttribute("error", "员工不存在");
                request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "查询员工信息失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, rs);
        }
    }

    private void addEmployee(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        User user = (User) session.getAttribute("user");

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            String name = request.getParameter("name");
            String department = request.getParameter("department");
            String position = request.getParameter("position");
            String salaryStr = request.getParameter("salary");
            String email = request.getParameter("email");
            String phone = request.getParameter("phone");
            String hireDateStr = request.getParameter("hireDate");

            // 验证必填字段
            if (name == null || name.trim().isEmpty() ||
                    department == null || department.trim().isEmpty() ||
                    position == null || position.trim().isEmpty() ||
                    salaryStr == null || salaryStr.trim().isEmpty() ||
                    hireDateStr == null || hireDateStr.trim().isEmpty()) {

                request.setAttribute("error", "请填写所有必填字段");
                request.getRequestDispatcher("/employee/add.jsp").forward(request, response);
                return;
            }

            conn = DatabaseUtil.getConnection();

            String sql = "INSERT INTO employees (name, department, position, salary, email, phone, hire_date, created_by) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, name.trim());
            pstmt.setString(2, department.trim());
            pstmt.setString(3, position.trim());
            pstmt.setBigDecimal(4, new BigDecimal(salaryStr));
            pstmt.setString(5, email != null ? email.trim() : null);
            pstmt.setString(6, phone != null ? phone.trim() : null);
            pstmt.setDate(7, Date.valueOf(LocalDate.parse(hireDateStr)));
            pstmt.setInt(8, user.getId());

            int result = pstmt.executeUpdate();
            if (result > 0) {
                response.sendRedirect(request.getContextPath() + "/employees");
            } else {
                request.setAttribute("error", "添加员工失败");
                request.getRequestDispatcher("/employee/add.jsp").forward(request, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "添加员工失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/add.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    private void updateEmployee(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            String idStr = request.getParameter("id");
            String name = request.getParameter("name");
            String department = request.getParameter("department");
            String position = request.getParameter("position");
            String salaryStr = request.getParameter("salary");
            String email = request.getParameter("email");
            String phone = request.getParameter("phone");
            String hireDateStr = request.getParameter("hireDate");

            // 验证必填字段
            if (idStr == null || idStr.trim().isEmpty() ||
                    name == null || name.trim().isEmpty() ||
                    department == null || department.trim().isEmpty() ||
                    position == null || position.trim().isEmpty() ||
                    salaryStr == null || salaryStr.trim().isEmpty() ||
                    hireDateStr == null || hireDateStr.trim().isEmpty()) {

                request.setAttribute("error", "请填写所有必填字段");
                request.getRequestDispatcher("/employee/edit.jsp").forward(request, response);
                return;
            }

            conn = DatabaseUtil.getConnection();

            String sql = "UPDATE employees SET name = ?, department = ?, position = ?, " +
                    "salary = ?, email = ?, phone = ?, hire_date = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, name.trim());
            pstmt.setString(2, department.trim());
            pstmt.setString(3, position.trim());
            pstmt.setBigDecimal(4, new BigDecimal(salaryStr));
            pstmt.setString(5, email != null ? email.trim() : null);
            pstmt.setString(6, phone != null ? phone.trim() : null);
            pstmt.setDate(7, Date.valueOf(LocalDate.parse(hireDateStr)));
            pstmt.setInt(8, Integer.parseInt(idStr));

            int result = pstmt.executeUpdate();
            if (result > 0) {
                response.sendRedirect(request.getContextPath() + "/employees");
            } else {
                request.setAttribute("error", "更新员工失败，员工可能不存在");
                request.getRequestDispatcher("/employee/edit.jsp").forward(request, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "更新员工失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/edit.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    private void deleteEmployee(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            int id = Integer.parseInt(request.getParameter("id"));
            conn = DatabaseUtil.getConnection();

            String sql = "DELETE FROM employees WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            int result = pstmt.executeUpdate();
            if (result > 0) {
                response.sendRedirect(request.getContextPath() + "/employees");
            } else {
                request.setAttribute("error", "删除员工失败，员工可能不存在");
                request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("error", "删除员工失败: " + e.getMessage());
            request.getRequestDispatcher("/employee/list.jsp").forward(request, response);
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    private void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}